1. Field of the Invention
The present invention relates to a relational database search system and method, and more particularly to a method of automatically generating an intermediate link table containing link information among relational database tables, and to a program storage medium.
2. Description of the Related Art
The conventional method of searching a relational database (RDB) is described below by referring to FIG. 1.
In FIG. 1, a storage device 3 stores a relational database and contains a basic table of the relational database. In FIG. 1, the storage device 3 stores, for example, a basic table 3g (table TECT), a basic table 3b (table IPPANT), and a basic table 3c (table SOURCET). A RDB server 2 manages the relational database, and includes an RDB basic process unit 2a and an SQL execution unit 2b. The RDB basic process unit 2a provides a basic process function for the relational database. The SQL execution unit 2b issues an SQL statement which is a query to the relational database. Each RDB client 1 is a user terminal which utilizes the relational database, and includes a GUI process unit 1a. The GUI process unit 1a provides a graphical user interface (GUI) for access to the relational database.
In the three above described examples, that is, the basic tables 3a, 3b, and 3c, a table TECT relates to technology and has a figure number field ZUB, a classification field BUN, a name field MEI, etc. The table IPPANT relates to a common symbol and has a circuit symbol field KAI, a figure number field ZUB, a registration date field TNEN, etc. The table SOURCET is a product information table for each circuit symbol and has a circuit symbol field KAI, a basic rank field KRAN, and an arrangement number field TEH, etc.
The table TECT and the table IPPANT are linked to each other through the field name ZUB. The table IPPANT and the table SOURCET are linked to each other through the field name KAI. The table TECT and the table SOURCET do not share any common field and therefore are not directly linked to each other.
When a user intends to extract the information in the field BUN (classification) of the table TECT using the value of TEH-10 of the field TEH (arrangement number) of the table SOURCET as a key, the following SQL statement is generated and executed by the SQL execution unit 2b.
SELECT BUN
FROM SOURCET.KAI=IPPANT.KAI AND IPPANT.ZUB=TECT.ZUB AND SOURCET.TEH=`TEH-01`;
where SELECT specifies an extraction item, FROM specifies a table name, and WHERE specifies a retrieval condition. In such a query statement, the information can only be extracted through the table IPPANT as an intermediate table because the table TECT and the table SOURCET have no direct link condition.
However, all common users do not constantly apprehend the links in all tables in the relational database, and may be informed of only TEH-01 (arrangement number) of data. Thus, if the user does not understand the entire relationship among all related tables, no appropriate SQL statement can be described.
Therefore, the function of easily searching a relational database by a user not familiar with the relational database is desired. In the conventional technology, the GUI process unit la of the RDB client 1 allows the user to input an item to be processed according to a displayed menu, etc., and to fill in a plurality of forms of standard SQL statements prepared by the GUI process unit 1a with items to be processed. Thus, the RDB server 2 is requested to execute the SQL statement.
However, in the search function provided by the conventional GUI process unit 1a using a menu, etc., extraction items and specification items of a search condition are preliminarily limited, and the user cannot optionally specify an extraction item or a retrieval condition. To allow the GUI process unit 1a to accept an optional extraction item and search condition, a sample of an SQL statement containing all possible combinations of items and conditions should be incorporated in a process logic of the GUI process unit 1a. However, this has not been practically realized.
Even if the function of the GUI process unit la can be flexible to some extent, it is limited to that a fixed SQL statement is allowed an optional query condition. Therefore, if the user retrieves data over a plurality of tables, he or she should be aware of the existence of an intermediate table and include it in the condition.
As described above, the user has to understand the table structure and field information in a relational database and also understand the linkage of tables and field information so that an appropriate SQL statement can be generated with an intermediate table taken into account.
If a new table is added and a link condition is changed, then an existing SQL statement and the related portion of an existing GUI process unit la should also be changed.
Even if an SQL statement is appropriate in syntax, information may be obtained through a plurality of link conditions, or it may be very difficult to know during the operation the deterioration of a process response with an increasing amount of time and data.